﻿using Chire.wechat;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Chire.ChireInter.Subject
{
    public class Subject_Action
    {
        #region 添加科目
        public void addSubjectManager(string name, string imgUrl)
        {
            // 连接数据库
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();
            string StrInsert = "";

            StrInsert = "insert into subject(name,img,create_time) values (@name,@img,@create_time)";
            SqlCommand cmd = new SqlCommand(StrInsert, sqlcon);
            // 添加参数并且设置参数值

            // 1. 作者id
            cmd.Parameters.Add("@name", SqlDbType.VarChar, 50);
            cmd.Parameters["@name"].Value = name;
            // 1. 日记标题
            cmd.Parameters.Add("@img", SqlDbType.VarChar, 500);
            cmd.Parameters["@img"].Value = imgUrl;

            cmd.Parameters.Add("@create_time", SqlDbType.VarChar, 350);
            cmd.Parameters["@create_time"].Value = DateTime.Now.ToString();

            // 执行插入数据的操作
            cmd.ExecuteNonQuery();
            sqlcon.Close();
        }
        #endregion

        #region 删除科目
        public void deleteSubjectWithId(string id)
        {
            // 连接数据库
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();
            string StrInsert = "";
            StrInsert = "delete from subject where id = '" + id + "'";
            SqlCommand cmd = new SqlCommand(StrInsert, sqlcon);
            // 添加参数并且设置参数值

            // 执行插入数据的操作
            cmd.ExecuteNonQuery();
            sqlcon.Close();
        }
        #endregion

        #region 获取所有科目
        public List<Subject_Model> getAllSubject()
        { 
         SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();

            string strselect = "select * from subject";
            //查询音乐信息
            DataTable dt = new DataTable();

            SqlDataAdapter dap = new SqlDataAdapter(strselect, sqlcon);
            dap.Fill(dt);
            int rows = dt.Rows.Count;

            List<Subject_Model> subjectList = new List<Subject_Model>();
            for (int i = 0; i < rows; i++)
            {
                string id = dt.Rows[i]["id"].ToString();
                string name = dt.Rows[i]["name"].ToString();
                string img = dt.Rows[i]["img"].ToString();
                string create_time = dt.Rows[i]["create_time"].ToString();
                long timeInter = Constance.Instance.ConvertDateTimeInt(Convert.ToDateTime(create_time));

                Subject_Model subjectModel = new Subject_Model()
                {
                    id = id,
                    name = name,
                    img = img,
                    createTime = timeInter,
                };
                subjectList.Add(subjectModel);
            }
            sqlcon.Close();
            return subjectList;
        }
        #endregion

        #region 修改学科
        public void updateSubject(string id,string key, string values) {
            //连接数据库
            SqlConnection sqlcon1 = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon1.Open();
            //修改数据信息
            string strSqls = "update subject set " + key + " = '" + values + "'  where id ='" + id + "'";

            SqlCommand cmd = new SqlCommand(strSqls, sqlcon1);
            //添加参数并且设置参数值
            cmd.ExecuteNonQuery();
            sqlcon1.Close();
        }
        #endregion

        #region 查看学科信息
        public Subject_Model getSubjectInfoWithId(string id)
        {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();

            string strselect = "select top 1 * from subject where id = '" + id + "'";
            SqlCommand sqlcmd = new SqlCommand(strselect, sqlcon);
            SqlDataReader dr = sqlcmd.ExecuteReader();
            Subject_Model subjectModel = new Subject_Model();
            if (dr.Read())
            {
                subjectModel.id = id;
                subjectModel.name = dr["name"].ToString();
                subjectModel.img = dr["img"].ToString();
                string date = dr["create_time"].ToString();
                subjectModel.createTime = Constance.Instance.ConvertDateTimeInt(Convert.ToDateTime(date));


            }
            sqlcon.Close();
            return subjectModel;
        }
        #endregion

    }
}